﻿/*
Copyright (C) 2009  Tomasz Chrzanowski

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Com.TomCdc.ChangeDataCapture
{
    /// <summary>
    /// Manages information of tracked tables and columns. Provides methods to retrieve the change history record.
    /// </summary>
    class SourceRecordHistoryProvider
    {
        /// <summary>
        /// Represents mapping from source table ID to source table columns.
        /// </summary>
        private readonly Dictionary<int, DataTable> _tableIdToColumns;

        /// <summary>
        /// Represents mapping from source table name to source table ID.
        /// </summary>
        private readonly Dictionary<string, int> _tableNameToId;

        /// <summary>
        /// Represents sql connection string to the store database.
        /// </summary>
        private readonly string _storeDbConnString;

        /// <summary>
        /// Represents sql query selecting source table columns and IDs.
        /// </summary>
        private const string SQL_SELECT_TABLE_COLUMNS = @"
            SELECT 
                [ID],
                [Name],
                [OrdinalPosition]
            FROM
                [SourceColumn]
            WHERE
                [SourceTableId] = @TableId";

        /// <summary>
        /// Represents sql query selectiong source tables and IDs.
        /// </summary>
        private const string SQL_SELECT_TABLES = @"
            SELECT 
                [ID],
                [Name]
            FROM
                [SourceTable]";

        /// <summary>
        /// Represents initial capacity of collection object that accommodates all tracked tables. 
        /// </summary>
        private const int INIT_TABLES_CAPACITY = 100;

        /// <summary>
        /// Initializes a new instance of <see cref="SourceRecordHistoryProvider"/> class with connection string to
        /// store database.
        /// </summary>
        /// <param name="storeConnString">A connection string to store database.</param>
        public SourceRecordHistoryProvider(string storeConnString)
        {
            if (string.IsNullOrEmpty(storeConnString))
            {
                throw new ArgumentException("The storeConnString connot be null or empty");
            }

            _storeDbConnString = storeConnString;

            _tableIdToColumns = new Dictionary<int, DataTable>();

            _tableNameToId = new Dictionary<string, int>(INIT_TABLES_CAPACITY, StringComparer.OrdinalIgnoreCase);
        }

        /// <summary>
        /// Gets the source record change history for record ID and table name stored in <paramref name="srcRecord"/>
        /// parameter.
        /// </summary>
        /// <param name="srcRecord">A <see cref="SourceRecord"/> object containg source table name and 
        /// source record ID.</param>
        /// <returns>The <see cref="DataTable"/> that contains a record change history.</returns>
        public DataTable GetSrcRecordHistory(SourceRecord srcRecord)
        {
            // validates input arguments
            if (srcRecord == null)
            {
                throw new ArgumentNullException("srcRecord");
            }

            /*
            if (srcRecord.SourceRowId < 0)
            {
                throw new ArgumentOutOfRangeException("The SourceRowId property of " + 
                    "srcRecord argument cannot be less than 0.");
            }
            */

            // loades tracked tables list
            LoadTables();

            // validates table name name passed in the argument
            if (!_tableNameToId.ContainsKey(srcRecord.SourceTableName))
            {
                throw new ArgumentException("The property SourceTableName of srcRecord argument " +
                    " contains not tracked table.");
            }

            // declares method return object
            DataTable result = new DataTable();

            // prepares sql objects to retreive change history of the requested source record
            using (SqlConnection sqlConn = new SqlConnection(_storeDbConnString))
            using (SqlCommand sqlCmd = new SqlCommand("SelectChangeHistory", sqlConn))
            using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd))
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlConn.Open();
                sqlCmd.Parameters.Add(new SqlParameter("@SourceTableId", _tableNameToId[srcRecord.SourceTableName]));
                sqlCmd.Parameters.Add(new SqlParameter("@SourceRowID", srcRecord.SourceRowId));
                sqlAdapter.Fill(result);
            }

            return result;
        }

        /// <summary>
        /// Gets collection of a source table columns.
        /// </summary>
        /// <param name="tableName">A source table name.</param>
        /// <returns>The <see cref="DataTable"/> containing a table columns.</returns>
        public DataTable GetTableColumns(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentException("The tableName argument cannot be null or empty.");
            }

            LoadTables();

            if (!_tableNameToId.ContainsKey(tableName))
            {
                throw new CdcViewerException("The table name: " + tableName +
                    " doesn't exist in the list of tracked tables.");
            }

            LoadColumns(_tableNameToId[tableName]);

            return _tableIdToColumns[_tableNameToId[tableName]];
        }

        /// <summary>
        /// Gets collection of source tables. Contains only tables which have been tracked for any time period since
        /// system installation.
        /// </summary>
        /// <returns>The collection of source tables.</returns>
        public Dictionary<string, int> GetTables()
        {
            LoadTables();
            return _tableNameToId;
        }

        /// <summary>
        /// Loads source tables from store databse into <see cref="_tableNameToId"/> collection.
        /// </summary>
        private void LoadTables()
        {
            // lazy loader, method called just before the data needed
            if (_tableNameToId.Count > 0)
            {
                return;
            }

            using (SqlConnection sqlConn = new SqlConnection(_storeDbConnString))
            using (SqlCommand sqlCmd = new SqlCommand(SQL_SELECT_TABLES, sqlConn))
            {
                sqlConn.Open();

                using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
                {
                    int colIdIdx = sqlReader.GetOrdinal("ID");
                    int colNameIdx = sqlReader.GetOrdinal("Name");

                    while (sqlReader.Read())
                    {
                        _tableNameToId.Add(sqlReader.GetString(colNameIdx), sqlReader.GetInt32(colIdIdx));
                    }
                }
            }
        }

        /// <summary>
        /// Loads columns of source table given in argument <paramref name="forTableId"/>.
        /// </summary>
        /// <param name="forTableId">An ID of source table.</param>
        private void LoadColumns(int forTableId)
        {
            if (forTableId <= 0)
            {
                throw new ArgumentOutOfRangeException("The forTableId argument must be greater than 0.");
            }

            // lazy loader pattern, if table already loaded from database end method
            if (_tableIdToColumns.ContainsKey(forTableId))
            {
                return;
            }

            using (SqlConnection sqlConn = new SqlConnection(_storeDbConnString))
            using (SqlCommand sqlCmd = new SqlCommand(SQL_SELECT_TABLE_COLUMNS, sqlConn))
            using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd))
            {
                sqlCmd.Parameters.Add(new SqlParameter("@tableId", forTableId));
                sqlConn.Open();

                DataTable dt = new DataTable();
                sqlAdapter.Fill(dt);
                _tableIdToColumns.Add(forTableId, dt);
            }
        }
    }
}
